const mysql = require('mysql2/promise')
const config = require('./config')

async function fixDatabase () {
  try {
    const connection = await mysql.createConnection({
      host: config.database.host,
      port: config.database.port,
      user: config.database.user,
      password: config.database.password,
      database: config.database.name
    })

    console.log('🔧 开始修复数据库结构...')

    // 1. 检查当前authors表结构
    console.log('\n📋 检查当前authors表结构:')
    try {
      const [structure] = await connection.execute('DESCRIBE authors')
      console.table(structure)

      // 如果authors表结构不正确，重新创建
      console.log('\n🗑️ 删除错误的authors表...')
      await connection.execute('DROP TABLE IF EXISTS authors')
      console.log('✅ authors表已删除')
    } catch (error) {
      console.log('authors表不存在，将创建新表')
    }

    // 2. 创建正确的authors表
    console.log('\n📝 创建正确的authors表...')
    await connection.execute(`
      CREATE TABLE authors (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        avatar_url VARCHAR(500) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)
    console.log('✅ authors表创建成功')

    // 3. 插入示例作者数据
    console.log('\n👥 插入作者数据...')
    const authors = [
      {
        name: '唐家三少',
        description: '中国网络文学作家，作品包括《斗罗大陆》、《绝世唐门》等。'
      },
      {
        name: '辰东',
        description:
          '网络文学作家，代表作品有《完美世界》、《遮天》、《圣墟》等。'
      },
      {
        name: '天蚕土豆',
        description: '网络文学作家，代表作品有《斗破苍穹》、《武动乾坤》等。'
      },
      {
        name: '我吃西红柿',
        description:
          '网络文学作家，代表作品有《盘龙》、《星辰变》、《莽荒纪》等。'
      },
      {
        name: '猫腻',
        description:
          '网络文学作家，代表作品有《庆余年》、《将夜》、《择天记》等。'
      }
    ]

    for (const author of authors) {
      await connection.execute(
        'INSERT INTO authors (name, description) VALUES (?, ?)',
        [author.name, author.description]
      )
    }
    console.log('✅ 作者数据插入完成')

    // 4. 检查novels表是否需要添加author_id字段
    console.log('\n📚 检查novels表结构...')
    const [novelStructure] = await connection.execute('DESCRIBE novels')
    const hasAuthorId = novelStructure.some(
      field => field.Field === 'author_id'
    )

    if (!hasAuthorId) {
      console.log('📝 为novels表添加author_id字段...')
      await connection.execute(
        'ALTER TABLE novels ADD COLUMN author_id INT DEFAULT NULL'
      )
      await connection.execute(
        'ALTER TABLE novels ADD INDEX idx_author_id (author_id)'
      )
      await connection.execute(
        'ALTER TABLE novels ADD FOREIGN KEY (author_id) REFERENCES authors(id)'
      )
      console.log('✅ author_id字段添加成功')
    } else {
      console.log('✅ novels表已有author_id字段')
    }

    // 5. 更新现有小说的author_id
    console.log('\n🔄 更新现有小说的作者关联...')

    // 获取所有小说和作者
    const [novels] = await connection.execute(
      'SELECT id, title, author FROM novels'
    )
    const [authorsData] = await connection.execute(
      'SELECT id, name FROM authors'
    )

    // 创建作者名称到ID的映射
    const authorMap = {}
    authorsData.forEach(author => {
      authorMap[author.name] = author.id
    })

    // 更新小说的author_id
    for (const novel of novels) {
      const authorId = authorMap[novel.author]
      if (authorId) {
        await connection.execute(
          'UPDATE novels SET author_id = ? WHERE id = ?',
          [authorId, novel.id]
        )
        console.log(`✅ 更新《${novel.title}》的作者ID: ${authorId}`)
      } else {
        console.log(
          `⚠️ 未找到作者《${novel.author}》的ID，跳过《${novel.title}》`
        )
      }
    }

    // 6. 检查并修复其他可能的问题
    console.log('\n🔍 检查novels表的其他字段...')
    const hasLikeCount = novelStructure.some(
      field => field.Field === 'like_count'
    )
    const hasFavoriteCount = novelStructure.some(
      field => field.Field === 'favorite_count'
    )

    if (!hasLikeCount && !hasFavoriteCount) {
      console.log('📝 添加like_count字段...')
      await connection.execute(
        'ALTER TABLE novels ADD COLUMN like_count INT DEFAULT 0'
      )
      await connection.execute(
        'ALTER TABLE novels ADD INDEX idx_like_count (like_count)'
      )
      console.log('✅ like_count字段添加成功')
    }

    // 7. 显示最终结果
    console.log('\n📊 最终的authors表数据:')
    const [finalAuthors] = await connection.execute('SELECT * FROM authors')
    console.table(finalAuthors)

    console.log('\n📚 novels表与authors的关联:')
    const [novelAuthorJoin] = await connection.execute(`
      SELECT n.id, n.title, n.author, a.name as author_name, n.author_id 
      FROM novels n 
      LEFT JOIN authors a ON n.author_id = a.id 
      LIMIT 10
    `)
    console.table(novelAuthorJoin)

    await connection.end()
    console.log('\n🎉 数据库修复完成！')
  } catch (error) {
    console.error('❌ 数据库修复失败:', error.message)
    console.error(error)
  }
}

fixDatabase()
